package himinbjorg

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"time"

	"github.com/emersion/go-imap"

	"github.com/mattn/go-sqlite3"
	_ "github.com/mattn/go-sqlite3"
)

type NoMessageError struct {
	MessageID string
}

func (e NoMessageError) Error() string {
	return "no message " + e.MessageID
}

type ArchiveEntry struct {
	This     Message
	Previous Message
	Next     []Message
}

func Migrate(dbPath string) (*sql.DB, error) {
	sql.Register("sqlite3_extended",
		&sqlite3.SQLiteDriver{
			ConnectHook: func(conn *sqlite3.SQLiteConn) error {
				return conn.RegisterFunc("regexp", func(re, s string) (bool, error) {
					return regexp.MatchString(re, s)
				}, true)
			},
		},
	)

	home, err := os.UserHomeDir()
	if err != nil {
		return nil, fmt.Errorf("while getting user home dir: %w", err)
	}
	possibleDbDirs := []string{
		"/var/lib/asgard",
		home + "/.local/state/asgard",
		".",
	}

	if dbPath != "" {
		possibleDbDirs = append([]string{filepath.Dir(dbPath)}, possibleDbDirs...)
	}

	finalDbPath := ""
	for _, possibleDbDir := range possibleDbDirs {
		dirInfo, err := os.Stat(possibleDbDir)
		if err == nil && dirInfo.IsDir() {
			if filepath.Dir(dbPath) == possibleDbDir && dbPath != "" {
				finalDbPath = dbPath
			} else {
				finalDbPath = possibleDbDir + "/asgard.db"
			}
			break
		}
	}
	if finalDbPath == "" {
		return nil, fmt.Errorf("no suitable db directory found")
	}
	db, err := open(finalDbPath)
	_, err = db.Exec(`create table tyr_knownAddresses(address_from text, address_to text, ban boolean, unique(address, direction))`)
	if err != nil && err.Error() != "table tyr_knownAddresses already exists" {
		return nil, err
	}
	_, err = db.Exec(`create table tyr_locks(address text unique, token text, date date)`)
	if err != nil && err.Error() != "table tyr_locks already exists" {
		return nil, err
	}

	_, err = db.Exec(`create table mimir_archive(message_id text primary key, subject text, body text, date datetime, in_reply_to text, dkim_status bool, sender text, category text, root_id text, foreign key(in_reply_to) references mimir_archive(message_id))`)
	if err != nil && err.Error() != "table mimir_archive already exists" {
		return nil, err
	}
	_, err = db.Exec(`create table mimir_recipients(root_message_id text, recipient text, primary key(root_message_id, recipient), foreign key(root_message_id) references mimir_archive(message_id))`)
	if err != nil && err.Error() != "table mimir_recipients already exists" {
		return nil, err
	}

	_, err = db.Exec(`alter table tyr_locks add column recipient text`)
	if err != nil && err.Error() != "duplicate column name: recipient" {
		return nil, err
	}

	return db, nil
}

func open(dbPath string) (*sql.DB, error) {
	path, err := filepath.Abs(dbPath)
	if err != nil {
		return nil, err
	}
	db, err := sql.Open("sqlite3_extended", path)
	if err != nil {
		return nil, err
	}
	return db, nil
}

func GetAddressLock(db *sql.DB, address string) (Lock, error) {
	address = strings.ToLower(address)
	lock := Lock{
		Address: address,
	}
	row := db.QueryRow(`select token, date from tyr_locks where address = ?`, address)
	err := row.Scan(&lock.Token, &lock.Date)
	if err == sql.ErrNoRows {
		return Lock{}, nil
	} else {
		return lock, err
	}
}

func GetLock(db *sql.DB, token string) (Lock, error) {
	lock := Lock{
		Token: token,
	}
	row := db.QueryRow(`select address, date from tyr_locks where token = ?`, token)
	err := row.Scan(&lock.Address, &lock.Date)
	if err == sql.ErrNoRows {
		return Lock{}, nil
	} else {
		return lock, err
	}
}

func ListLocks(db *sql.DB) ([]Lock, error) {
	locks := []Lock{}
	rows, err := db.Query(`select address, token from tyr_locks`)
	if err != nil {
		return locks, err
	}
	for rows.Next() {
		lock := Lock{}
		err := rows.Scan(&lock.Address, &lock.Token)
		if err != nil {
			return locks, err
		}
		locks = append(locks, lock)
	}
	return locks, nil
}

func InsertLock(db *sql.DB, lock Lock) error {
	_, err := db.Exec(`insert into tyr_locks values(?, ?, ?, ?) on
	                  conflict(address) do nothing`,
		lock.Address, lock.Token, lock.Date, lock.Recipient)
	return err
}

func DeleteLock(db *sql.DB, lock Lock) error {
	_, err := db.Exec(`delete from tyr_locks where address = ?`, lock.Address)
	return err
}

func UpdateLock(db *sql.DB, lock Lock) error {
	_, err := db.Exec(`update tyr_locks set date = ? where address = ?`, lock.Date, lock.Address)
	return err
}

func GetKnownAddress(db *sql.DB, address string) ([]KnownAddress, error) {
	knownAddresses := []KnownAddress{}

	rows, err := db.Query(`select address_from, address_to, ban from tyr_knownAddresses where ? REGEXP address_from`, address)
	if err != nil {
		return []KnownAddress{}, err
	}
	for rows.Next() {
		knownAddress := KnownAddress{}
		err := rows.Scan(&knownAddress.AddressFrom, &knownAddress.AddressTo, &knownAddress.Ban)
		if err != nil {
			return []KnownAddress{}, err
		}
		knownAddresses = append(knownAddresses, knownAddress)
	}
	return knownAddresses, nil
}

func InsertKnownAddress(db *sql.DB, address KnownAddress) error {
	_, err := db.Exec(`insert into tyr_knownAddresses values(?, ?, ?) on
	                  conflict(address_to, address_from) do nothing`,
		address.AddressFrom, address.AddressTo, address.Ban)
	return err
}

func AddArchiveEntry(db *sql.DB, messageID, category, subject string, body []byte, date time.Time, inReplyTo string, dkim bool, sender *imap.Address, messageBytes string) error {
	var rootID string
	row := db.QueryRow(`select root_id from mimir_archive where message_id = ?`, inReplyTo)
	err := row.Scan(&rootID)
	if err != nil {
		if err == (sql.ErrNoRows) {
			rootID = messageID
		} else {
			return err
		}
	}

	_, err = db.Exec(`insert into mimir_archive values(?, ?, ?, ?, ?, ?, ?, ?, ?)`, messageID, subject, body, date, inReplyTo, dkim, MakeNameAddress(sender, false), category, rootID)

	return err
}

func UpdateRecipients(db *sql.DB, address *imap.Address, msgID string) error {
	var (
		rootID    string
		recipient sql.NullString
	)
	row := db.QueryRow(`select root_id, recipient from mimir_archive left outer join mimir_recipients on(root_id == root_message_id) where message_id = ? and sender = ?`, msgID, MakeNameAddress(address, false))
	err := row.Scan(&rootID, &recipient)
	if err != nil {
		return err
	}
	if !recipient.Valid {
		_, err = db.Exec(`insert into mimir_recipients values(?, ?)`, rootID, address.Address())
	}
	return err
}

func GetRecipients(db *sql.DB, messageID string, sender *imap.Address) ([]string, error) {
	recipients := []string{}

	rows, err := db.Query(`select recipient from mimir_archive join mimir_recipients on(root_id == root_message_id) where message_id = ?`, messageID)
	if err != nil {
		return recipients, err
	}
	for rows.Next() {
		var recipient string
		err := rows.Scan(&recipient)
		if err != nil {
			return recipients, err
		}
		if recipient != sender.Address() {
			recipients = append(recipients, recipient)
		}
	}
	return recipients, nil
}

func GetArchivedThread(db *sql.DB, msgID string) ([]Message, error) {
	messages := []Message{}
	rows, err := db.Query(`select subject, body, date, dkim_status, sender, category, message_id from mimir_archive where root_id = ? order by date asc`, msgID)
	if err != nil {
		return messages, fmt.Errorf("while selecting thread: %w", err)
	}
	for rows.Next() {
		message := Message{}
		err := rows.Scan(&message.Subject, &message.Body, &message.Date, &message.Dkim, &message.Sender, &message.Category, &message.ID)
		if err != nil {
			return messages, fmt.Errorf("while scanning message in thread: %w", err)
		}
		messages = append(messages, message)
	}
	return messages, err
}

func GetArchivedThreads(db *sql.DB, page int64) ([]Message, int, error) {
	messages := []Message{}
	var numThreads int
	row := db.QueryRow(`select count(*) from mimir_archive where root_id == message_id`)
	err := row.Scan(&numThreads)
	if err != nil {
		return messages, 0, fmt.Errorf("while selecting count: %w", err)
	}
	if numThreads == 0 {
		return messages, numThreads, nil
	}
	rows, err := db.Query(`select subject, date, sender, category, message_id, root_id, CASE WHEN LENGTH(body) > 256 THEN substr(body,1,256) || '…' ELSE body END from mimir_archive where root_id = message_id order by date desc limit 12 offset ?`, (page-1)*12)
	if err != nil {
		return messages, 0, fmt.Errorf("while selecting threads: %w", err)
	}
	for rows.Next() {
		msg := Message{}
		err := rows.Scan(&msg.Subject, &msg.Date, &msg.Sender, &msg.Category, &msg.ID, &msg.Thread, &msg.Body)
		if err != nil {
			return messages, 0, fmt.Errorf("while scanning message: %w", err)
		}
		messages = append(messages, msg)
	}
	return messages, numThreads, nil
}
